﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class WelfareLoanAgreementBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);       

        public void insert(string cont_ref_no,string cont_date,int emp_seq,int loan_seq, double cont_amount,string cont_status,string loan_ref_no, string loan_acc_no,string start_date,double pay_month,int amt_period, string remarks, int created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@cont_ref_no", cont_ref_no);
                dbManager.AddParameters(1, "@cont_date", DateTime.Parse(cont_date));
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@loan_seq", loan_seq);
                dbManager.AddParameters(4, "@cont_amount", cont_amount);
                dbManager.AddParameters(5, "@cont_status", cont_status);
                dbManager.AddParameters(6, "@loan_ref_no", loan_ref_no);
                dbManager.AddParameters(7, "@loan_acc_no", loan_acc_no);
                dbManager.AddParameters(8, "@start_date", DateTime.Parse(start_date));
                dbManager.AddParameters(9, "@pay_month", pay_month);
                dbManager.AddParameters(10, "@amt_period", amt_period);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Today);

                string sql;
                
                sql = "INSERT INTO HRM_WF_CONTRACTS "
                + "(CONT_REF_NO,CONT_DATE,EMP_SEQ, LOAN_SEQ,CONT_AMOUNT,CONT_STATUS,LOAN_REF_NO, LOAN_ACC_NO"
                    + ",START_DATE,PAY_MONTH,AMT_PERIOD,REMARKS,RECORD_STATUS,CREATED_BY,CREATED_WHEN)"
                + " VALUES "
                + "(@cont_ref_no,@cont_date,@emp_seq,@loan_seq,@cont_amount,@cont_status,@loan_ref_no, @loan_acc_no"
                    + ",@start_date,@pay_month,@amt_period,@remarks,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);   

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }
        

        public void Update(int cont_seq, string cont_ref_no, string cont_date, int emp_seq, int loan_seq, double cont_amount, string cont_status, string loan_ref_no, string loan_acc_no, string start_date, double pay_month, int amt_period, string remarks, int updated_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@cont_ref_no", cont_ref_no);
                dbManager.AddParameters(1, "@cont_date", DateTime.Parse(cont_date));
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@loan_seq", loan_seq);
                dbManager.AddParameters(4, "@cont_amount", cont_amount);                
                dbManager.AddParameters(5, "@cont_status", cont_status);
                dbManager.AddParameters(6, "@loan_ref_no", loan_ref_no);
                dbManager.AddParameters(7, "@loan_acc_no", loan_acc_no);
                dbManager.AddParameters(8, "@start_date", DateTime.Parse(start_date));
                dbManager.AddParameters(9, "@pay_month", pay_month);
                dbManager.AddParameters(10, "@amt_period", amt_period);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@cont_seq", cont_seq);
                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@updated_by", updated_by);
                dbManager.AddParameters(15, "@updated_when", DateTime.Today);
               // dbManager.AddParameters(5, "@int_rate", int_rate);
                
                string sql;

                sql = " update hrm_wf_contracts "
                + " set cont_ref_no=@cont_ref_no,"
                + " cont_date=@cont_date,"
                + " emp_seq=@emp_seq,"
                + " loan_seq=@loan_seq,"
                + " cont_amount=@cont_amount,"
                + " cont_status=@cont_status,"
                + " loan_ref_no=@loan_ref_no,"
                + " loan_acc_no=@loan_acc_no,"
                + " start_date=@start_date,"
                + " pay_month=@pay_month,"
                + " amt_period=@amt_period,"
                + " remarks=@remarks,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by, "
                + " updated_when=@updated_when"
                + " where cont_seq=@cont_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }


        public void Delete(int cont_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cont_seq", cont_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;


                sql = " update hrm_wf_contracts set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cont_seq = @cont_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Select(int emp_type_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                string sql;
                sql = " SELECT CONCAT(np.PREFIX_NAME,'',e.FIRST_NAME,'  ',e.LAST_NAME) AS FULL_NAME, empt.emp_type_seq, empt.emp_type_name"
                    + ", c.CONT_SEQ, c.CONT_REF_NO, c.CONT_DATE, c.START_DATE, c.CONT_STATUS, c.AMT_PERIOD"
                    + ", cast(concat((select count(cont_dtls_seq) "
                                    + " from hrm_wf_contract_dtls"
                                    + " where cont_seq = c.cont_seq)"
                            + ",'/', c.amt_period) as char) AS amt_period_pay"
                    + ", c.REMARKS, c.EMP_SEQ, cs.CONT_STAT_NAME, l.LOAN_NAME, c.LOAN_SEQ"
                    + ", c.EMP_SEQ, c.LOAN_REF_NO, c.LOAN_ACC_NO, c.CONT_AMOUNT, c.PAY_MONTH, e.EMP_TYPE_SEQ"
                    + ", (select count(cont_dtls_seq) "
                            + " from hrm_wf_contract_dtls "
                            + " where cont_seq = c.cont_seq) AS COUNT_PAYMENT"
                     + " FROM HRM_WF_CONTRACTS c"
                     + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.EMP_SEQ = e.EMP_SEQ"
                     + " LEFT JOIN hrm_ps_employee_types empt on e.emp_type_seq = empt.emp_type_seq"
                     + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                     + " LEFT JOIN HRM_WF_CONTRACT_STATUSES cs ON c.CONT_STATUS = cs.CONT_STAT_SEQ"
                     + " LEFT JOIN HRM_WF_LOANS l ON c.LOAN_SEQ = l.LOAN_SEQ"
                     + " WHERE c.RECORD_STATUS = @record_status"
                     + " AND e.EMP_TYPE_SEQ = @emp_type_seq";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public Boolean chkDup(int loan_seq, int emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_contracts"
                    + " where loan_seq = " + loan_seq
                    + " and emp_seq = " + emp_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupContRef(string cont_ref_no)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_contracts"

                    + " where cont_ref_no = '" + cont_ref_no + "'"
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupContRefEdit(string cont_ref_no, int cont_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_contracts"

                    + " where cont_ref_no = '" + cont_ref_no + "'"
                    + " and cont_seq not like " + cont_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public DataTable SelectEmp()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
                    + " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
                    + " emp.emp_seq "
                    + " FROM"
                    + " hrm_ps_employees AS emp"
                    + " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
                    + " where emp.record_status = '" + SystemInfo._ActiveRecordStatus + "'";





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public DataTable SelectEmpByID(int id)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
            + " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
            + " emp.emp_seq "
            + " FROM"
            + " hrm_ps_employees AS emp"
            + " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
            + " where emp.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
            + " and emp_seq=" + id;





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable GetLoan(int loan_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from hrm_wf_loans  "
                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                    + " and loan_seq= " + loan_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }
        public DataTable SelectDialogLoan()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = " SELECT l.LOAN_SEQ, l.LOAN_NAME, l.FINANCE_SEQ, f.FINANCE_NAME"
                    + " FROM HRM_WF_LOANS l"
                    + " INNER JOIN HRM_WF_FINANCES f ON l.FINANCE_SEQ = f.FINANCE_SEQ"
                    + " WHERE l.RECORD_STATUS = @RecordStatus";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectConStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_contract_statuses where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
    }
}
